﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PurchaseOrderInstallmentDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PurchaseOrderInstallmentDAL()
        {

        }

        public IList<PurchaseOrderInstallmentInfo> GetPurchaseOrderInstallmentFromParent(int po_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentInfo> infoList = new List<PurchaseOrderInstallmentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_po_seq", po_seq);

                strSQL = "SELECT o.*"
                            + ", coalesce((select sum(coalesce(net_amount,0)) from icm_purchase_order_installment_item where icm_po_install_seq = o.icm_po_install_seq),0) AS SUM_AMOUNT"
                            + ", dt.ICM_DUR_TYPE_NAME"
                            + ", po.ICM_PO_TYPE_SEQ"
                        + " FROM ICM_PURCHASE_ORDER_INSTALLMENT o"
                            + " INNER JOIN ICM_PURCHASE_ORDER po ON o.ICM_PO_SEQ = po.ICM_PO_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON o.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                        + " WHERE o.ICM_PO_SEQ = @icm_po_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentInfo info = new PurchaseOrderInstallmentInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        string strPoType = dr[i]["ICM_PO_TYPE_SEQ"].ToString();

                        info.Seq = (Convert.IsDBNull(dr[i]["ICM_PO_INSTALL_SEQ"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_PO_INSTALL_SEQ"]));
                        info.RequireDuration = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["REQUIRE_DURATION"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationType.Name = dr[i]["ICM_DUR_TYPE_NAME"].ToString();
                        info.DurationDescr = (info.RequireDuration != null ? info.RequireDuration + " " + infoDurationType.Name + (strPoType == "1" ? "นับจากวันที่รับใบสั่ง" : "นับจากวันที่ลงนามในสัญญา") : "");
                        info.RequireDate = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)Convert.ToDateTime(dr[i]["REQUIRE_DATE"]));
                        info.DurationDateDescr = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? "" : Convert.ToDateTime(dr[i]["REQUIRE_DATE"]).ToString("dd/MM/yyyy"));
                        info.InstallNumber = (Convert.IsDBNull(dr[i]["ICM_PO_INSTALL_NUMBER"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_PO_INSTALL_NUMBER"]));
                        info.DeliveryDate = (Convert.IsDBNull(dr[i]["DELIVERY_DATE"]) ? null : (Nullable<DateTime>)Convert.ToDateTime(dr[i]["DELIVERY_DATE"]));
                        info.Remarks = dr[i]["REMARKS"].ToString();
                        info.RedemptionAmount = (Convert.IsDBNull(dr[i]["REDEMPTION_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["REDEMPTION_AMOUNT"]));
                        info.DeductionAmount = (Convert.IsDBNull(dr[i]["DEDUCTION_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["DEDUCTION_AMOUNT"]));
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["SUM_AMOUNT"]));

                        info.InfoDurationType = infoDurationType;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseOrderInstallmentInfo> GetPurchaseOrderAndInstallmentForDeliveryNote(string search_text)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseOrderInstallmentInfo> infoList = new List<PurchaseOrderInstallmentInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@search_text", search_text);
                dbManager.AddParameters(1, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@no_status", SystemInfo._NoStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + "SELECT po.ICM_PO_SEQ"
                            + ", i.ICM_PO_INSTALL_SEQ"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 THEN CONCAT('ใบสั่ง', pt.ICM_PC_TYPE_NAME) ELSE CONCAT('สัญญา', pt.ICM_PC_TYPE_NAME) END AS PO_TYPE"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 AND po.CAN_INSTALLMENT = @no_status THEN NULL ELSE i.ICM_PO_INSTALL_NUMBER END AS ICM_PO_INSTALL_NUMBER"
                            + ", po.ICM_PO_REF_NO AS REF_NO"
                            + ", po.ICM_PO_NAME AS NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", i.DELIVERY_DATE"
                            + ", (select sum(coalesce(net_amount,0))"
                                + " from icm_purchase_order_installment_item"
                                + " where icm_po_install_seq = i.icm_po_install_seq)"
                            + " AS SUM_AMOUNT"
                            + ", po.RECORD_STATUS"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT i ON po.ICM_PO_SEQ = i.ICM_PO_SEQ"
                            + " INNER JOIN ICM_BIDDER bd ON po.ICM_BIDDER_SEQ = bd.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bd.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " UNION ALL "
                        + " SELECT po.ICM_PO_SEQ"
                            + ", i.ICM_PO_INSTALL_SEQ"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 THEN CONCAT('ใบสั่ง', pt.ICM_PC_TYPE_NAME) ELSE CONCAT('สัญญา', pt.ICM_PC_TYPE_NAME) END AS PO_TYPE"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 AND po.CAN_INSTALLMENT = @no_status THEN NULL ELSE i.ICM_PO_INSTALL_NUMBER END AS ICM_PO_INSTALL_NUMBER"
                            + ", po.ICM_PO_REF_NO AS REF_NO"
                            + ", po.ICM_PO_NAME AS NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", i.DELIVERY_DATE"
                            + ", (select sum(coalesce(net_amount,0))"
                                + " from icm_purchase_order_installment_item"
                                + " where icm_po_install_seq = i.icm_po_install_seq)"
                            + " AS SUM_AMOUNT"
                            + ", po.RECORD_STATUS"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT i ON po.ICM_PO_SEQ = i.ICM_PO_SEQ"
                            + " INNER JOIN ICM_BIDDER bd ON po.ICM_BIDDER_SEQ = bd.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID b ON bd.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + ") main"
                        + " WHERE RECORD_STATUS = @approve_status"
                            + " AND CONCAT(ICM_PO_SEQ,'-', ICM_PO_INSTALL_SEQ) NOT IN"
                                + "(select concat(icm_po_seq,'-', icm_po_install_seq) from icm_delivery_note where record_status <> @delete_status)"
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (PO_TYPE LIKE CONCAT('%', @search_text , '%')"
                                    + " or ICM_PO_INSTALL_NUMBER LIKE CONCAT('%', @search_text , '%')"
                                    + " or REF_NO LIKE CONCAT('%', @search_text , '%')"
                                    + " or NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " or ICM_CON_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " or DELIVERY_DATE LIKE CONCAT('%', @search_text , '%')"
                                    + " or SUM_AMOUNT LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseOrderInstallmentInfo info = new PurchaseOrderInstallmentInfo();
                        PurchaseOrderInfo infoPo = new PurchaseOrderInfo();
                        SysPoTypeInfo infoPoType = new SysPoTypeInfo();
                        MasterContactInfo infoMasContact = new MasterContactInfo();
                        MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                        BidderInfo infoBidder = new BidderInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PO_INSTALL_SEQ"]);
                        infoPo.Seq = Convert.ToInt32(dr[i]["ICM_PO_SEQ"]);
                        info.InstallNumber = (Convert.IsDBNull(dr[i]["ICM_PO_INSTALL_NUMBER"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_PO_INSTALL_NUMBER"]));
                        infoPoType.Name = dr[i]["PO_TYPE"].ToString();
                        infoPo.Ref_No = dr[i]["REF_NO"].ToString();
                        infoPo.Name = dr[i]["NAME"].ToString();
                        infoMasContact.Name_th = dr[i]["ICM_CON_NAME"].ToString();
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["SUM_AMOUNT"]));
                        info.DeliveryDate = (Convert.IsDBNull(dr[i]["DELIVERY_DATE"]) ? null : (Nullable<DateTime>)Convert.ToDateTime(dr[i]["DELIVERY_DATE"]));

                        infoBidder.InfoMasContact = infoMasContact;
                        infoPo.InfoPoType = infoPoType;
                        infoPo.InfoBidder = infoBidder;
                        info.InfoPurchaseOrder = infoPo;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public PurchaseOrderInstallmentInfo GetPurchaseOrderInstallmentFromDeliveryNote(int install_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            PurchaseOrderInstallmentInfo info = new PurchaseOrderInstallmentInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@install_seq", install_seq);
                dbManager.AddParameters(1, "@no_status", SystemInfo._NoStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + "SELECT po.ICM_PO_SEQ"
                            + ", i.ICM_PO_INSTALL_SEQ"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 THEN CONCAT('ใบสั่ง', pt.ICM_PC_TYPE_NAME) ELSE CONCAT('สัญญา', pt.ICM_PC_TYPE_NAME) END AS PO_TYPE"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 AND po.CAN_INSTALLMENT = @no_status THEN NULL ELSE i.ICM_PO_INSTALL_NUMBER END AS ICM_PO_INSTALL_NUMBER"
                            + ", po.ICM_PO_REF_NO AS REF_NO"
                            + ", po.ICM_PO_NAME AS NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", i.DELIVERY_DATE"
                            + ", (select sum(coalesce(net_amount,0))"
                                + " from icm_purchase_order_installment_item"
                                + " where icm_po_install_seq = i.icm_po_install_seq)"
                            + " AS SUM_AMOUNT"
                            + ", po.RECORD_STATUS"
                            + ", i.REDEMPTION_AMOUNT"
                            + ", i.DEDUCTION_AMOUNT"
                            + ", po.YEAR_TYPE_SEQ"
                            + ", po.BGM_YEAR"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT i ON po.ICM_PO_SEQ = i.ICM_PO_SEQ"
                            + " INNER JOIN ICM_BIDDER bd ON po.ICM_BIDDER_SEQ = bd.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bd.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " UNION ALL "
                        + " SELECT po.ICM_PO_SEQ"
                            + ", i.ICM_PO_INSTALL_SEQ"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 THEN CONCAT('ใบสั่ง', pt.ICM_PC_TYPE_NAME) ELSE CONCAT('สัญญา', pt.ICM_PC_TYPE_NAME) END AS PO_TYPE"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 AND po.CAN_INSTALLMENT = @no_status THEN NULL ELSE i.ICM_PO_INSTALL_NUMBER END AS ICM_PO_INSTALL_NUMBER"
                            + ", po.ICM_PO_REF_NO AS REF_NO"
                            + ", po.ICM_PO_NAME AS NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", i.DELIVERY_DATE"
                            + ", (select sum(coalesce(net_amount,0))"
                                + " from icm_purchase_order_installment_item"
                                + " where icm_po_install_seq = i.icm_po_install_seq)"
                            + " AS SUM_AMOUNT"
                            + ", po.RECORD_STATUS"
                            + ", i.REDEMPTION_AMOUNT"
                            + ", i.DEDUCTION_AMOUNT"
                            + ", po.YEAR_TYPE_SEQ"
                            + ", po.BGM_YEAR"
                        + " FROM ICM_PURCHASE_ORDER po"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT i ON po.ICM_PO_SEQ = i.ICM_PO_SEQ"
                            + " INNER JOIN ICM_BIDDER bd ON po.ICM_BIDDER_SEQ = bd.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID b ON bd.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + ") main"
                        + " WHERE ICM_PO_INSTALL_SEQ = @install_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    PurchaseOrderInfo infoPo = new PurchaseOrderInfo();
                    SysPoTypeInfo infoPoType = new SysPoTypeInfo();
                    MasterContactInfo infoMasContact = new MasterContactInfo();
                    MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                    BidderInfo infoBidder = new BidderInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();

                    infoYearType.Seq = Convert.ToInt32(dr[0]["YEAR_TYPE_SEQ"]);
                    infoPo.BgmYear = Convert.ToInt32(dr[0]["BGM_YEAR"]);
                    info.Seq = Convert.ToInt32(dr[0]["ICM_PO_INSTALL_SEQ"]);
                    infoPo.Seq = Convert.ToInt32(dr[0]["ICM_PO_SEQ"]);
                    info.InstallNumber = (Convert.IsDBNull(dr[0]["ICM_PO_INSTALL_NUMBER"]) ? null : (Nullable<int>)Convert.ToInt32(dr[0]["ICM_PO_INSTALL_NUMBER"]));
                    infoPoType.Name = dr[0]["PO_TYPE"].ToString();
                    infoPo.Ref_No = dr[0]["REF_NO"].ToString();
                    infoPo.Name = dr[0]["NAME"].ToString();
                    infoMasContact.Name_th = dr[0]["ICM_CON_NAME"].ToString();
                    info.SumAmount = (Convert.IsDBNull(dr[0]["SUM_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["SUM_AMOUNT"]));
                    info.RedemptionAmount = (Convert.IsDBNull(dr[0]["REDEMPTION_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["REDEMPTION_AMOUNT"]));
                    info.DeductionAmount = (Convert.IsDBNull(dr[0]["DEDUCTION_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["DEDUCTION_AMOUNT"]));
                    info.DeliveryDate = (Convert.IsDBNull(dr[0]["DELIVERY_DATE"]) ? null : (Nullable<DateTime>)Convert.ToDateTime(dr[0]["DELIVERY_DATE"]));

                    infoPo.InfoYearType = infoYearType;
                    infoBidder.InfoMasContact = infoMasContact;
                    infoPo.InfoPoType = infoPoType;
                    infoPo.InfoBidder = infoBidder;
                    info.InfoPurchaseOrder = infoPo;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public PurchaseOrderInstallmentInfo GetPurchaseOrderInstallmentWithDeliveryNote(int dn_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            PurchaseOrderInstallmentInfo info = new PurchaseOrderInstallmentInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@dn_seq", dn_seq);
                dbManager.AddParameters(1, "@no_status", SystemInfo._NoStatus);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + "SELECT po.ICM_PO_SEQ"
                            + ", i.ICM_PO_INSTALL_SEQ"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 THEN CONCAT('ใบสั่ง', pt.ICM_PC_TYPE_NAME) ELSE CONCAT('สัญญา', pt.ICM_PC_TYPE_NAME) END AS PO_TYPE"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 AND po.CAN_INSTALLMENT = @no_status THEN NULL ELSE i.ICM_PO_INSTALL_NUMBER END AS ICM_PO_INSTALL_NUMBER"
                            + ", po.ICM_PO_REF_NO AS REF_NO"
                            + ", po.ICM_PO_NAME AS NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", i.DELIVERY_DATE"
                            + ", (select sum(coalesce(net_amount,0))"
                                + " from icm_purchase_order_installment_item"
                                + " where icm_po_install_seq = i.icm_po_install_seq)"
                            + " AS SUM_AMOUNT"
                            + ", po.RECORD_STATUS"
                            + ", i.REDEMPTION_AMOUNT"
                            + ", i.DEDUCTION_AMOUNT"
                            + ", po.YEAR_TYPE_SEQ"
                            + ", po.BGM_YEAR"
                            + ", dn.ICM_DN_SEQ"
                            + ", po.CAN_INSTALLMENT"
                        + " FROM ICM_DELIVERY_NOTE dn"
                            + " INNER JOIN ICM_PURCHASE_ORDER po ON dn.ICM_PO_SEQ = po.ICM_PO_SEQ"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT i ON po.ICM_PO_SEQ = i.ICM_PO_SEQ"
                            + " INNER JOIN ICM_BIDDER bd ON po.ICM_BIDDER_SEQ = bd.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bd.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " UNION ALL "
                        + " SELECT po.ICM_PO_SEQ"
                            + ", i.ICM_PO_INSTALL_SEQ"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 THEN CONCAT('ใบสั่ง', pt.ICM_PC_TYPE_NAME) ELSE CONCAT('สัญญา', pt.ICM_PC_TYPE_NAME) END AS PO_TYPE"
                            + ", CASE WHEN po.ICM_PO_TYPE_SEQ = 1 AND po.CAN_INSTALLMENT = @no_status THEN NULL ELSE i.ICM_PO_INSTALL_NUMBER END AS ICM_PO_INSTALL_NUMBER"
                            + ", po.ICM_PO_REF_NO AS REF_NO"
                            + ", po.ICM_PO_NAME AS NAME"
                            + ", CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'), '')) AS ICM_CON_NAME"
                            + ", i.DELIVERY_DATE"
                            + ", (select sum(coalesce(net_amount,0))"
                                + " from icm_purchase_order_installment_item"
                                + " where icm_po_install_seq = i.icm_po_install_seq)"
                            + " AS SUM_AMOUNT"
                            + ", po.RECORD_STATUS"
                            + ", i.REDEMPTION_AMOUNT"
                            + ", i.DEDUCTION_AMOUNT"
                            + ", po.YEAR_TYPE_SEQ"
                            + ", po.BGM_YEAR"
                            + ", dn.ICM_DN_SEQ"
                            + ", po.CAN_INSTALLMENT"
                        + " FROM ICM_DELIVERY_NOTE dn"
                            + " INNER JOIN ICM_PURCHASE_ORDER po ON dn.ICM_PO_SEQ = po.ICM_PO_SEQ"
                            + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT i ON po.ICM_PO_SEQ = i.ICM_PO_SEQ"
                            + " INNER JOIN ICM_BIDDER bd ON po.ICM_BIDDER_SEQ = bd.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID b ON bd.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON bd.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP cg ON c.ICM_CON_GRP_SEQ = cg.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + ") main"
                        + " WHERE ICM_DN_SEQ = @dn_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    PurchaseOrderInfo infoPo = new PurchaseOrderInfo();
                    SysPoTypeInfo infoPoType = new SysPoTypeInfo();
                    MasterContactInfo infoMasContact = new MasterContactInfo();
                    MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                    BidderInfo infoBidder = new BidderInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();

                    infoYearType.Seq = Convert.ToInt32(dr[0]["YEAR_TYPE_SEQ"]);
                    infoPo.BgmYear = Convert.ToInt32(dr[0]["BGM_YEAR"]);
                    info.Seq = Convert.ToInt32(dr[0]["ICM_PO_INSTALL_SEQ"]);
                    infoPo.Seq = Convert.ToInt32(dr[0]["ICM_PO_SEQ"]);
                    info.InstallNumber = (Convert.IsDBNull(dr[0]["ICM_PO_INSTALL_NUMBER"]) ? null : (Nullable<int>)Convert.ToInt32(dr[0]["ICM_PO_INSTALL_NUMBER"]));
                    infoPoType.Name = dr[0]["PO_TYPE"].ToString();
                    infoPo.Ref_No = dr[0]["REF_NO"].ToString();
                    infoPo.Name = dr[0]["NAME"].ToString();
                    infoPo.CanInstallment = dr[0]["CAN_INSTALLMENT"].ToString();
                    infoMasContact.Name_th = dr[0]["ICM_CON_NAME"].ToString();
                    info.SumAmount = (Convert.IsDBNull(dr[0]["SUM_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["SUM_AMOUNT"]));
                    info.RedemptionAmount = (Convert.IsDBNull(dr[0]["REDEMPTION_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["REDEMPTION_AMOUNT"]));
                    info.DeductionAmount = (Convert.IsDBNull(dr[0]["DEDUCTION_AMOUNT"]) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["DEDUCTION_AMOUNT"]));
                    info.DeliveryDate = (Convert.IsDBNull(dr[0]["DELIVERY_DATE"]) ? null : (Nullable<DateTime>)Convert.ToDateTime(dr[0]["DELIVERY_DATE"]));

                    infoPo.InfoYearType = infoYearType;
                    infoBidder.InfoMasContact = infoMasContact;
                    infoPo.InfoPoType = infoPoType;
                    infoPo.InfoBidder = infoBidder;
                    info.InfoPurchaseOrder = infoPo;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }


        public int Insert(PurchaseOrderInstallmentInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            try
            {
                dbManagerTrans.CreateParameters(7);

                dbManagerTrans.AddParameters(0, "@icm_po_seq", info.InfoPurchaseOrder.Seq);
                dbManagerTrans.AddParameters(1, "@icm_po_install_number", info.InstallNumber);
                dbManagerTrans.AddParameters(2, "@require_duration", info.RequireDuration);
                dbManagerTrans.AddParameters(3, "@icm_dur_type_seq", info.InfoDurationType.Seq);
                dbManagerTrans.AddParameters(4, "@require_date", info.RequireDate);
                dbManagerTrans.AddParameters(5, "@created_by", info.InfoRecord.Created_by);
                dbManagerTrans.AddParameters(6, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_PURCHASE_ORDER_INSTALLMENT(ICM_PO_SEQ,ICM_PO_INSTALL_NUMBER,REQUIRE_DURATION,ICM_DUR_TYPE_SEQ, REQUIRE_DATE, DELIVERY_DATE, CREATED_BY,CREATED_WHEN)"
                        + " VALUES (@icm_po_seq, @icm_po_install_number, @require_duration, @icm_dur_type_seq, @require_date, @require_date, @created_by, @created_when)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "SELECT LAST_INSERT_ID()"
                       + " FROM ICM_PURCHASE_ORDER_INSTALLMENT";

                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }
            }
            catch
            {
                throw;
            }
            return lastSeq;
        }

        public void Insert(IList<PurchaseOrderInstallmentInfo> infoList)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            PurchaseOrderInstallmentItemDAL dal = new PurchaseOrderInstallmentItemDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_po_seq", infoList[0].InfoPurchaseOrder.Seq);
                strSQL = "DELETE FROM ICM_PURCHASE_ORDER_INSTALLMENT"
                    + " WHERE ICM_PO_SEQ = @icm_po_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(4);

                    dbManager.AddParameters(0, "@icm_po_seq", infoList[i].InfoPurchaseOrder.Seq);
                    dbManager.AddParameters(1, "@icm_po_install_number", infoList[i].InstallNumber);
                    dbManager.AddParameters(2, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManager.AddParameters(3, "@created_when", infoList[i].InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_PURCHASE_ORDER_INSTALLMENT(ICM_PO_SEQ,ICM_PO_INSTALL_NUMBER, CREATED_BY,CREATED_WHEN)"
                            + " VALUES (@icm_po_seq, @icm_po_install_number, @created_by, @created_when)";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                    strSQL = "SELECT LAST_INSERT_ID()"
                           + " FROM ICM_PURCHASE_ORDER_INSTALLMENT";

                    ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }

                    dal.Insert(infoList[i].InfoListItem, lastSeq, infoList[i], dbManager);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
        }

        public void Update(IList<PurchaseOrderInstallmentInfo> infoList)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(8);

                    dbManager.AddParameters(0, "@seq", infoList[i].Seq);
                    dbManager.AddParameters(1, "@icm_po_install_number", infoList[i].InstallNumber);
                    dbManager.AddParameters(2, "@require_duration", infoList[i].RequireDuration);
                    dbManager.AddParameters(3, "@icm_dur_type_seq", infoList[i].InfoDurationType.Seq);
                    dbManager.AddParameters(4, "@require_date", infoList[i].RequireDate);
                    dbManager.AddParameters(5, "@delivery_date", infoList[i].DeliveryDate);
                    dbManager.AddParameters(6, "@updated_by", infoList[i].InfoRecord.Updated_by);
                    dbManager.AddParameters(7, "@updated_when", infoList[i].InfoRecord.Updated_when);

                    strSQL = "UPDATE ICM_PURCHASE_ORDER_INSTALLMENT"
                        + " SET ICM_PO_INSTALL_NUMBER = @icm_po_install_number"
                        + ", REQUIRE_DURATION = @require_duration"
                        + ",ICM_DUR_TYPE_SEQ = @icm_dur_type_seq"
                        + ", REQUIRE_DATE = @require_date"
                        + ", DELIVERY_DATE = @delivery_date"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + " WHERE ICM_PO_INSTALL_SEQ = @seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
        }
    }
}
